import os
import pandas as pd
from flask import Flask, request, jsonify
import numpy as np
import numpy_financial as npf
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
import decimal
import uuid
from datetime import datetime
import re

decimal.getcontext().prec = 10  # 设置高精度
decimal.getcontext().rounding = decimal.ROUND_HALF_UP  # 使用与Excel一致的四舍五入规则

# Database connection setup
username = 'root'
password = '123456'
host = '192.168.0.189'
port = '3306'
database = 'shale-gas'
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}')

default_data = {
    "项目指标": [
        "产量（亿方）", "商品率（%）", "商品量（亿方）", "价格（元/千方）", "补贴（元/千方）",
        "城建税、教育附加费率", "矿产资源税率（%）", "矿产资源补偿率（%）",
        "操作费（元/千方）", "管理费（元/千方）", "销售费率", "所得税率",
        "营运期投资", "流动资金", "增值税率", "增值系数",
        "安全费用系数（元/千方）", "建设投资", "财务费用（弃置成本财务费用）", "弃置费", "销售费"
    ],
    "第1年": [5.5149, 0.96, 5.29, 1170, 200, 0.12, 0, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 163964, 0, 0, 0],
    "第2年": [9.6081, 0.96, 9.22, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第3年": [3.6296, 0.96, 3.48, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第4年": [2.2372, 0.96, 2.15, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第5年": [1.7147, 0.96, 1.65, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第6年": [1.4523, 0.96, 1.39, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第7年": [1.2888, 0.96, 1.24, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第8年": [1.1564, 0.96, 1.11, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第9年": [1.0492, 0.96, 1.01, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第10年": [0.9625, 0.96, 0.92, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第11年": [0.8927, 0.96, 0.86, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第12年": [0.8371, 0.96, 0.80, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第13年": [0.7935, 0.96, 0.76, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第14年": [0.7521, 0.96, 0.72, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第15年": [0.7128, 0.96, 0.68, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第16年": [0.6754, 0.96, 0.65, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第17年": [0.6399, 0.96, 0.61, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第18年": [0.6062, 0.96, 0.58, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第19年": [0.5741, 0.96, 0.55, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第20年": [0.5437, 0.96, 0.52, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0]
}


def load_gas_production_and_investment_data():
    """
    从数据库加载气井各年份产量数据和建设期投资，并转换为亿方单位（除以 10000）。
    建设期的建设投资使用 total_cost 值，其余年份设定为 0。
    如果未找到数据或出现数据库错误，将返回包含 None 的默认列表和 0 的建设投资数据。
    """
    query = """
        SELECT 
            gas_production_year1, gas_production_year2, gas_production_year3, gas_production_year4, gas_production_year5,
            gas_production_year6, gas_production_year7, gas_production_year8, gas_production_year9, gas_production_year10,
            gas_production_year11, gas_production_year12, gas_production_year13, gas_production_year14, gas_production_year15,
            gas_production_year16, gas_production_year17, gas_production_year18, gas_production_year19, gas_production_year20,
            total_cost
        FROM gas_well_para_optimization LIMIT 1
    """

    try:
        with engine.connect() as conn:
            result = conn.execute(text(query))
            row = result.fetchone()

        if row:
            # 读取各年产量数据并转换为亿方单位（除以 10000）
            production_data = [value / 10000 if value is not None else None for value in row[:-1]]
            # 读取建设期投资
            construction_investment = row[-1] if row[-1] is not None else 0
            # 其余年份建设投资设为 0
            investment_data = [construction_investment] + [0] * 20

            return production_data, investment_data

        # 未查询到任何数据的情况
        print("No production or investment data found in gas_well_para_optimization.")
        return [None] * 20, [0] * 21

    except SQLAlchemyError as e:
        # 数据库连接或查询出现错误
        print(f"Database error while loading production and investment data: {e}")
        return [None] * 20, [0] * 21

def new_load_gas_production_and_investment_data():
    '''
    从新数据库表加载气井各字段数据。
    '''
    query = '''
        SELECT 
            instalments, yield, commodity_rate, quantity_goods, price, subsidy,
            urban_construction_tax, mineral_resources_tax_rate, mineral_resource_compensation_rate, 
            operating_fee, management_fee, selling_rate, income_tax_rate, 
            operational_investment, liquidity, selling_fee, vat_rate, 
            value_factor, security_fee_factor, construction_investment, 
            finance_expenses, disposal_fee
        FROM gas_npv_output_temporary_new
    '''

    try:
        with engine.connect() as conn:
            result = conn.execute(text(query))
            rows = result.fetchall()

        if rows:
            columns = [
                'instalments', 'yield', 'commodity_rate', 'quantity_goods', 'price', 'subsidy',
                'urban_construction_tax', 'mineral_resources_tax_rate', 'mineral_resource_compensation_rate',
                'operating_fee', 'management_fee', 'selling_rate', 'income_tax_rate',
                'operational_investment', 'liquidity', 'selling_fee', 'vat_rate',
                'value_factor', 'security_fee_factor', 'construction_investment',
                'finance_expenses', 'disposal_fee'
            ]
            data = pd.DataFrame(rows, columns=columns)
            return data

        print("No data found in gas_npv_output_eur_new.")
        return pd.DataFrame(columns=columns)

    except SQLAlchemyError as e:
        print(f"Database error while loading data: {e}")
        return pd.DataFrame()


def new_calculate_output(df):
    try:
        columns = [
            "年份", "现金流入", "产品营业收入", "回收流动资金", "其它收入（补贴）", "现金流出", "建设投资",
            "经营成本费用", "二级单位管理费", "操作成本", "其他成本（安全生产费）", "营业税金及附加", "增值税",
            "城建税、教育附加费", "矿产资源补偿费", "矿产资源税", "利润总额（不含所得税）", "所得税", "净利润",
            "所得税后净现金流", "累计税后净现金流量", "投资折旧", "销售费用"
        ]

        # 创建空的 DataFrame，指定列名但无行数据
        output_data_df = pd.DataFrame(columns=columns)

        # 检查并替换所有计算列中的 None 值，并确保所有数值都是 float 类型
        numeric_columns = [col for col in df.columns if col != 'instalments']
        for col in numeric_columns:
            print(f"处理列 {col}，检查是否有 None 值: {df[col].isnull().any()}")
            df[col] = df[col].fillna(0)
            df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0).astype(float)

        # 确保总投资是 float 类型
        cumulative_net_cash_flow = 0.0
        total_investment = float(df["construction_investment"].sum())

        df['year_num'] = df['instalments'].str.extract(r'第(\d+)年').astype(int)
        df = df.sort_values(by='year_num')

        # 确保最后一年的弃置费是总建设投资的5%
        last_index = len(df) - 1
        df.at[last_index, 'disposal_fee'] = total_investment * 0.05

        cumulative_depreciation = 0.0

        for i, row in df.iterrows():
            # 计算每个指标的值
            quantity_goods = float(row['yield']) * float(row['commodity_rate'])
            product_revenue = quantity_goods * float(row['price']) * 10.0
            subsidy = float(row['subsidy']) * quantity_goods * 10.0
            cash_inflow = product_revenue + subsidy + float(row['liquidity'])

            # 成本计算
            management_cost = float(row['yield']) * float(row['management_fee']) * 10.0
            operating_cost = quantity_goods * float(row['operating_fee']) * 10.0
            safety_cost = float(row['security_fee_factor']) * float(row['yield']) * 10.0
            total_operating_costs = management_cost + float(row['selling_fee']) + operating_cost + safety_cost

            # 税金计算
            value_added_tax = cash_inflow * float(row['vat_rate']) * float(row['value_factor'])
            urban_education_surcharge = value_added_tax * float(row['urban_construction_tax'])
            resource_compensation_fee = product_revenue * float(row['mineral_resource_compensation_rate'])
            resource_tax = product_revenue * float(row['mineral_resources_tax_rate'])
            business_tax_surcharges = urban_education_surcharge + resource_compensation_fee + resource_tax

            # 现金流出
            cash_outflow = (
                    float(row['construction_investment']) + float(row['operational_investment']) +
                    total_operating_costs + business_tax_surcharges + float(row['finance_expenses']) + float(
                row['disposal_fee'])
            )

            # 计算第i年产气量
            current_yield = float(row['yield'])

            # 计算总产气量
            total_yield = float(df['yield'].sum())

            # 计算第1年至第i年累计建设投资
            cumulative_investment_to_current = float(df.loc[:i, 'construction_investment'].sum())

            # 折旧计算 - 区分第1年和其他年份
            if i == 0:  # 第1年
                # 对第1年，使用第1年产气量/总产气量 * 第1年建设投资
                current_investment = float(row['construction_investment'])
                depreciation = (current_yield / total_yield) * current_investment
            else:  # 第2年及以后
                # 计算第i年至结束时的累计产气量
                future_yield = float(df.loc[i:, 'yield'].sum())

                # 计算第1年至第i-1年累计产气量
                past_yield = float(df.loc[:i - 1, 'yield'].sum())

                # 计算分母：第i年至结束时的累计产气量
                remaining_yield = future_yield

                # 未折旧投资额
                undepreciated_investment = cumulative_investment_to_current - cumulative_depreciation

                # 折旧计算 - 使用Excel公式的逻辑
                if remaining_yield > 0 and undepreciated_investment > 0:
                    depreciation = undepreciated_investment * (current_yield / remaining_yield)
                    # 确保折旧不超过未折旧投资额
                    depreciation = min(depreciation, undepreciated_investment)
                else:
                    # 如果剩余产量为0或已折旧完毕，则折旧为0
                    depreciation = 0.0

            # 更新累计折旧
            cumulative_depreciation += depreciation

            '''
            # 计算从当前年份开始的剩余产量
            remaining_yield = float(df['yield'].iloc[i:].sum())

            # 折旧和累计现金流
            if remaining_yield > 0:
                depreciation = (float(total_investment) - float(cumulative_depreciation)) * (
                        float(row['yield']) / remaining_yield)
            else:
                depreciation = 0.0
            cumulative_depreciation += depreciation'''


            # 利润计算
            profit_total = cash_inflow - (total_operating_costs + business_tax_surcharges + depreciation +
                                          float(row['finance_expenses']) + float(row['disposal_fee']))
            income_tax = profit_total * float(row['income_tax_rate']) if profit_total > 0 else 0.0
            net_profit = profit_total - income_tax
            net_cash_flow_after_tax = cash_inflow - cash_outflow - income_tax
            cumulative_net_cash_flow += net_cash_flow_after_tax

            # 创建当前年份数据的字典并转换为 DataFrame
            year_data = pd.DataFrame([{
                "年份": row['instalments'],
                "现金流入": cash_inflow,
                "产品营业收入": product_revenue,
                "回收流动资金": float(row['liquidity']),
                "其它收入（补贴）": subsidy,
                "现金流出": cash_outflow,
                "建设投资": float(row['construction_investment']),
                "经营成本费用": total_operating_costs,
                "二级单位管理费": management_cost,
                "操作成本": operating_cost,
                "其他成本（安全生产费）": safety_cost,
                "营业税金及附加": business_tax_surcharges,
                "增值税": value_added_tax,
                "城建税、教育附加费": urban_education_surcharge,
                "矿产资源补偿费": resource_compensation_fee,
                "矿产资源税": resource_tax,
                "利润总额（不含所得税）": profit_total,
                "所得税": income_tax,
                "净利润": net_profit,
                "所得税后净现金流": net_cash_flow_after_tax,
                "累计税后净现金流量": cumulative_net_cash_flow,
                "投资折旧": depreciation,
                "销售费用": float(row['selling_fee'])
            }])

            # 使用 pd.concat 将 year_data 追加到 output_data_df
            output_data_df = pd.concat([output_data_df, year_data], ignore_index=True)

        # 计算财务指标
        total_npv = new_calculate_total_npv(output_data_df)
        internal_rate_of_return = new_calculate_irr(output_data_df)
        payback_period = new_calculate_payback_period(output_data_df)

        return output_data_df, total_npv, internal_rate_of_return, payback_period

    except Exception as e:
        import traceback
        print(f"计算错误详情: {traceback.format_exc()}")
        raise ValueError(f"Error in calculate_output: {str(e)}")


def new_calculate_total_npv(output_data, discount_rate=0.06):
    # 提取年份数字并排序
    output_data["年份数值"] = output_data["年份"].str.extract(r'第(\d+)年').astype(int)
    sorted_data = output_data.sort_values(by="年份数值")

    # 将"所得税后净现金流"列中的数据转换为浮点数
    cash_flows = sorted_data["所得税后净现金流"].apply(float).values
    years = sorted_data["年份数值"].values

    # 确保年份是从低到高排序的
    if len(years) > 0:
        # 检查是否从第0年或第1年开始
        start_year = years[0]

        # 正确计算NPV（与Excel一致）
        npv = 0
        for i, cf in enumerate(cash_flows):
            # 根据实际年份计算折现期数
            discount_period = years[i] - start_year + 1
            npv += cf / ((1 + discount_rate) ** discount_period)
    else:
        npv = 0

    # 删除临时列
    if "年份数值" in output_data.columns:
        output_data.drop("年份数值", axis=1, inplace=True)

    return npv


def new_calculate_irr(output_data):
    # 提取年份数字并排序
    output_data["年份数值"] = output_data["年份"].str.extract(r'第(\d+)年').astype(int)
    sorted_data = output_data.sort_values(by="年份数值")

    # 将"所得税后净现金流"列中的数据转换为浮点数数组
    cash_flows = sorted_data["所得税后净现金流"].apply(float).values

    # 删除临时列
    if "年份数值" in output_data.columns:
        output_data.drop("年份数值", axis=1, inplace=True)

    # 检查现金流是否为空
    if len(cash_flows) == 0:
        return None

    # 检查现金流是否同时包含正值和负值
    if not (np.any(cash_flows > 0) and np.any(cash_flows < 0)):
        return None  # 如果全是正值或全是负值，IRR无法计算

    try:
        # 使用numpy_financial的irr函数，其计算方式与Excel的IRR函数一致
        irr = npf.irr(cash_flows)
        return irr
    except:
        return None  # 捕获可能的计算错误


def new_calculate_payback_period(output_data):
    # 提取年份数字并排序
    output_data["年份数值"] = output_data["年份"].str.extract(r'第(\d+)年').astype(int)
    sorted_data = output_data.sort_values(by="年份数值")

    # 重新计算累计现金流（确保排序后累计值是正确的）
    sorted_data["重新计算累计现金流"] = sorted_data["所得税后净现金流"].astype(float).cumsum()
    cumulative_cash_flows = sorted_data["重新计算累计现金流"].values
    years = sorted_data["年份"].values

    # 删除临时列
    if "年份数值" in output_data.columns:
        output_data.drop("年份数值", axis=1, inplace=True)

    # 遍历年度现金流，找到第一个正现金流出现的年份
    for i, cash_flow in enumerate(cumulative_cash_flows):
        if cash_flow >= 0:  # 找到第一个非负的累计现金流
            if i > 0 and cumulative_cash_flows[i - 1] < 0:
                # 计算精确的回收期（线性插值）
                prev_cf = cumulative_cash_flows[i - 1]
                current_cf = cash_flow
                fraction = abs(prev_cf) / (current_cf - prev_cf)

                # 提取年份数字
                prev_year = int(re.search(r'第(\d+)年', years[i - 1]).group(1))
                current_year = int(re.search(r'第(\d+)年', years[i]).group(1))

                # 计算精确的回收期
                exact_payback = prev_year + fraction * (current_year - prev_year)
                return f"{exact_payback:.2f}年"
            return years[i]

    # 如果没有找到正现金流，说明没有转正
    return "大于最大年份"


def insert_data_into_gas_npv_table(output_data_df, engine):
    # 添加 `id`, `create_time`, `update_time` 等必需字段
    output_data_df['id'] = [str(uuid.uuid4()) for _ in range(len(output_data_df))]  # 为每一行生成唯一的UUID
    output_data_df['update_by'] = ''  # 更新人，默认为空
    output_data_df['update_time'] = datetime.now()  # 当前时间作为更新时间
    output_data_df['sys_org_code'] = ''  # 所属部门，默认为空
    output_data_df['create_by'] = ''  # 创建人，默认为空
    output_data_df['create_time'] = datetime.now()  # 当前时间作为创建时间

    # 将列名重命名为与数据库表结构一致
    output_data_df = output_data_df.rename(columns={
        "年份": "instalments",
        "现金流入": "cash_inflows",
        "产品营业收入": "product_revenue",
        "回收流动资金": "recycling_working_capital",
        "其它收入（补贴）": "other_income",
        "现金流出": "cash_outflows",
        "建设投资": "construction_investment",
        "经营成本费用": "operating_expenses",
        "二级单位管理费": "management_fee_secondary",
        "操作成本": "operating_costs",
        "其他成本（安全生产费）": "other_costs",
        "营业税金及附加": "business_tax_surcharges",
        "增值税": "vat",
        "城建税、教育附加费": "urban_construction_tax",
        "矿产资源补偿费": "mineral_resources_compensation_fee",
        "矿产资源税": "mineral_resources_tax",
        "所得税": "income_tax",
        "净利润": "net_profit",
        "利润总额（不含所得税）":"gross_profit",
        "所得税后净现金流": "net_cash_flow_tax",
        "累计税后净现金流量": "accumulated_taxes",
        "投资折旧": "investment_depreciation",
        "销售费用": "selling_fees"
    })

    try:
        # 先清空表内容
        with engine.begin() as conn:
            conn.execute(text("TRUNCATE TABLE gas_npv_output_eur_new"))
            print("已清空 gas_npv_output_eur_new 表。")

        # 将数据插入到数据库表中
        with engine.begin() as conn:
            output_data_df.to_sql('gas_npv_output_eur_new', con=conn, if_exists='append', index=False)

        print("数据已成功插入到 gas_npv_output_eur_new 表中。")

    except SQLAlchemyError as e:
        print(f"数据插入错误: {e}")
        raise

def save_to_sql_multi(output_data_df, engine):
    # 添加 `id`, `create_time`, `update_time` 等必需字段
    output_data_df['id'] = [str(uuid.uuid4()) for _ in range(len(output_data_df))]  # 为每一行生成唯一的UUID
    output_data_df['update_by'] = ''  # 更新人，默认为空
    output_data_df['update_time'] = datetime.now()  # 当前时间作为更新时间
    output_data_df['sys_org_code'] = ''  # 所属部门，默认为空
    output_data_df['create_by'] = ''  # 创建人，默认为空
    output_data_df['create_time'] = datetime.now()  # 当前时间作为创建时间

    # 将列名重命名为与数据库表结构一致
    output_data_df = output_data_df.rename(columns={
        "年份": "instalments",
        "现金流入": "cash_inflows",
        "产品营业收入": "product_revenue",
        "回收流动资金": "recycling_working_capital",
        "其它收入（补贴）": "other_income",
        "现金流出": "cash_outflows",
        "建设投资": "construction_investment",
        "经营成本费用": "operating_expenses",
        "二级单位管理费": "management_fee_secondary",
        "操作成本": "operating_costs",
        "其他成本（安全生产费）": "other_costs",
        "营业税金及附加": "business_tax_surcharges",
        "增值税": "vat",
        "城建税、教育附加费": "urban_construction_tax",
        "矿产资源补偿费": "mineral_resources_compensation_fee",
        "矿产资源税": "mineral_resources_tax",
        "所得税": "income_tax",
        "净利润": "net_profit",
        "利润总额（不含所得税）":"gross_profit",
        "所得税后净现金流": "net_cash_flow_tax",
        "累计税后净现金流量": "accumulated_taxes",
        "投资折旧": "investment_depreciation",
        "销售费用": "selling_fees"
    })
    try:
        with engine.begin() as conn:
            output_data_df.to_sql('gas_npv_output_multiple_scenarios', con=conn, if_exists='append', index=False)

        print("数据已成功插入到 gas_npv_output_multiple_scenarios表中。")

    except SQLAlchemyError as e:
        print(f"数据插入错误: {e}")
        raise